﻿CREATE PROCEDURE dbo.SP_Errors_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @Code Char (5)  =  NULL 
, @Description NVarChar (256)  =  NULL 
, @ErrorId Int   =  NULL 
, @Message NVarChar (256)  =  NULL 
	AS
			WITH CTE_Errors AS
			( 
			SELECT
 							Code,
								Description,
								ErrorId,
								Message,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
			 
						WHEN @OrderColume='Code' THEN Code
						 
						WHEN @OrderColume='Description' THEN Description
												WHEN @OrderColume='ErrorId' THEN ErrorId
						 
						WHEN @OrderColume='Message' THEN Message
									ELSE 
							    ErrorId
					        	END
			) AS "RowNumber"
  			FROM Errors 
			where 
								(Code LIKE @Code OR @Code Is Null)
										AND(Description LIKE @Description OR @Description Is Null)
										AND(ErrorId = @ErrorId OR @ErrorId Is Null)
										AND(Message LIKE @Message OR @Message Is Null)
								)
			SELECT  *
			FROM CTE_Errors
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			